package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;
import core.CoreDao;
import core.db.DBConnectionUtil;

/**
 * 数据完整性校验Dao
 * @author jack
 *
 */
public class DataCompleteDao {
	
	/**
	 * 查询状态数据不完整的数量
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return delayAmount noStatusAmount
	 */
	public List<JSONObject> queryStatusAmounts(CoreDao dao, String beginTime, String endTime) {
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<JSONObject> list = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer();
			sql.append(" 		SELECT ");
			sql.append(" 			session_id  AS sessionId ");
			sql.append(" 		FROM ");
			sql.append(" 			t_meet_info i ");
			sql.append(" 		WHERE ");
			sql.append(" 			i.ability_type IN ( ");
			sql.append(" 				'TalksV2', 'Dial_YonYou', 'YonYouVoice', 'YonYouAudeo', 'Ivr_YonYou', ");
			sql.append(" 				'YonYouVoice70', 'YonYouVoice140', 'YonYouAudeo70', 'YonYouAudeo140' ");
			sql.append(" 			) ");
			sql.append(" 			AND i.create_time >= ? ");
			sql.append(" 			AND i.create_time < ? ");
			sql.append(" 			AND NOT EXISTS (");
			sql.append(" 				SELECT session_id FROM t_call_status s WHERE s.vSessionsId = i.session_id ");
			sql.append(" 			) ");
			//查询数据
			list = dao.serviceJsonSql(con, sql.toString(), 
					new Object[] { 
						beginTime, endTime
					});
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return list;		
	}
	
	/**
	 * 查询话单数据不完整的数量
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return delayAmount noStatusAmount
	 */
	public List<JSONObject> queryBillAmounts(CoreDao dao, String beginTime, String endTime) {
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<JSONObject> list = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer();
			sql.append(" 		SELECT ");
			sql.append(" 			s.vSessionsId  AS sessionId ");
			sql.append(" 		FROM ");
			sql.append(" 			t_call_status s ");
			sql.append(" 		WHERE ");
			sql.append(" 			s.vServiceType IN ( ");
			sql.append(" 				'TalksV2', 'Dial_YonYou', 'YonYouVoice', 'YonYouAudeo', 'Ivr_YonYou', ");
			sql.append(" 				'YonYouVoice70', 'YonYouVoice140', 'YonYouAudeo70', 'YonYouAudeo140' ");
			sql.append(" 			) ");
			sql.append(" 			AND s.vCallState='DISCONNECTED' ");
			sql.append(" 			AND s.createTime >= ? ");
			sql.append(" 			AND s.createTime < ? ");
			sql.append(" 			AND NOT EXISTS (");
			sql.append(" 				SELECT session_id FROM t_meet_ims_callback c WHERE c.session_id = s.vSessionsId ");
			sql.append(" 			) ");
			//查询数据
			list = dao.serviceJsonSql(con, sql.toString(), 
					new Object[] { 
						beginTime, endTime
					});
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return list;		
	}
	
	/**
	 * 查询语音通知积压数量
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return delayAmount noStatusAmount
	 */
	public List<JSONObject> queryVoiceDelayAmounts(CoreDao dao, String beginTime, String endTime) {
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<JSONObject> list = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer();
			sql.append(" 		SELECT ");
			sql.append(" 			n.meet_ims_id AS voiceId ");
			sql.append(" 		FROM ");
			sql.append(" 			t_send_notice n ");
			sql.append(" 		WHERE ");
			sql.append(" 			n.`status` = '-1' ");
			sql.append(" 			AND n.schedule_send_time >= ? ");
			sql.append(" 			AND n.schedule_send_time < ? ");
			//查询数据
			list = dao.serviceJsonSql(con, sql.toString(), 
					new Object[] { 
						beginTime, endTime
					});
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return list;		
	}
	
}
